LEFT and RIGHT JOIN Solutions

This section is a walkthrough of those final two problems in the previous concept. First, another look at the two tables we are working with:

INNER JOIN Question

The questions are aimed to assure you have a conceptual idea of what is happening with LEFT and INNER JOIN s before you need to use them for more difficult problems.

For an INNER JOIN like the one here:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
JOIN State s
ON c.countryid = s.countryid;

We are essentially JOIN ing the matching PK - FK links from the two tables, as shown in the below image.

The resulting table will look like:

LEFT JOIN Question

The questions are aimed to assure you have a conceptual idea of what is happening with LEFT and INNER JOIN s before you need to use them for more difficult problems.

For a LEFT JOIN like the one here:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
LEFT JOIN State s
ON c.countryid = s.countryid;

We are essentially JOIN ing the matching PK - FK links from the two tables, as we did before, but we are also pulling all the additional rows from the Country table even if they don't have a match in the State table. Therefore, we obtain all the rows of the INNER JOIN , but we also get additional rows from the table in the FROM .

The resulting table will look like:

FINAL LEFT JOIN Note

If we were to flip the tables, we would actually obtain the same exact result as the JOIN statement:

SELECT c.countryid, c.countryName, s.stateName
FROM State s
LEFT JOIN Country c
ON c.countryid = s.countryid;

This is because if State is on the LEFT table, all of the rows exist in the RIGHT table again.

The resulting table will look like: